﻿CREATE PROCEDURE [dbo].[s_ShowSFKids]
	@TableName sysname,
	@principal_id sysname,
	@GrandParentID int = -1,
	@StateFromID int = 0,
	@Tabs int = 0
AS
SET NOCOUNT ON
DECLARE @CR varchar(2) SET @CR = '
'
DECLARE @StateName sysname,@KidID int,@Kids nvarchar(max),@Color varchar(50)

IF @GrandParentID = -1 BEGIN
	IF OBJECT_ID('tempdb..#ShowSFKids') IS NULL BEGIN
		CREATE TABLE #ShowSFKids(GrandParentID int,StateFromID int,KidID int,Tabs int)
		CREATE UNIQUE CLUSTERED INDEX #IX_ShowKids ON #ShowSFKids(GrandParentID,StateFromID,KidID,Tabs);
	END
	CREATE TABLE #Nodes(Pos int IDENTITY,Node nvarchar(4000))
	DECLARE cur CURSOR LOCAL READ_ONLY FOR
	SELECT sff.StateFromID,sff.NameFrom,sfc.Color FROM StateFlowFromByGroup(@TableName,@principal_id)sff
	INNER JOIN ts_StateFlowColor sfc ON sff.HasAccess = sfc.HasAccess AND 0 = sfc.Direction
	ORDER BY sff.StateFlowPosFrom
	OPEN cur
	FETCH NEXT FROM cur INTO @StateFromID,@StateName,@Color
	WHILE @@fetch_status = 0 BEGIN
		--PRINT '<MYTREE:TREENODE text="'+@StateName+'">'+@CR
		INSERT INTO #Nodes VALUES('<MYTREE:TREENODE DEFAULTSTYLE="'+@Color+'" tabID="'+CONVERT(varchar,@StateFromID)+'" id="'+CONVERT(varchar,@StateFromID)+'" text="'+@StateName+'">'+@CR)
		EXEC s_ShowSFKids @TableName,@principal_id,@StateFromID,@StateFromID,1
		--PRINT '</MYTREE:TREENODE>'+@CR
		INSERT INTO #Nodes VALUES('</MYTREE:TREENODE>'+@CR)
		FETCH NEXT FROM cur INTO @StateFromID,@StateName,@Color
	END
	CLOSE cur
	DEALLOCATE cur
	SELECT Node FROM #Nodes ORDER BY Pos
	RETURN
END


DECLARE @Count int, @Pos int,@Tabs_ int,@Tab char SELECT @Pos=0,@Tabs_=@Tabs+1,@Tab = '	'
DECLARE @KidsTable TABLE(Pos int IDENTITY, KidName sysname,KidID int,Color sysname)
INSERT INTO @KidsTable
SELECT sft.NameTo,sft.StateToID,Color FROM StateFlowToByGroup(@principal_id,@StateFromID) sft
INNER JOIN ts_StateFlowColor sfc ON sft.HasAccess = sfc.HasAccess AND SIGN(sft.Dir) = sfc.Direction
ORDER BY sft.StateFlowPosTo

DECLARE curKids CURSOR LOCAL FAST_FORWARD FOR SELECT KidName,KidID,Color FROM @KidsTable ORDER BY Pos

INSERT INTO #ShowSFKids SELECT @GrandParentID,@StateFromID,K.KidID,@Tabs
FROM @KidsTable K LEFT OUTER JOIN #ShowSFKids SFK
	ON SFK.GrandParentID = @GrandParentID AND
		 SFK.StateFromID = @StateFromID AND
		 SFK.KidID = K.KidID AND
		 SFK.Tabs = @Tabs
WHERE SFK.KidID IS NULL
OPTION(KEEP PLAN)

---SELECT TOP 1 @Pos=Pos,@StateName=KidName,@KidID=KidID,@Color=Color FROM @KidsTable WHERE Pos>@Pos
OPEN curKids
FETCH NEXT FROM curKids INTO @StateName,@KidID,@Color

WHILE @@fetch_status = 0 BEGIN
	---DELETE FROM #ShowSFKids WHERE GrandParentID=@GrandParentID AND StateFromID=@StateFromID AND KidID=@KidID AND Tabs > @Tabs
	SELECT @Count = COUNT(*) FROM #ShowSFKids WHERE GrandParentID=@GrandParentID AND StateFromID=@StateFromID AND KidID=@KidID AND Tabs<@Tabs OPTION(KEEP PLAN)
	IF @Count = 0
	BEGIN
		--PRINT REPLICATE(@Tab,@Tabs) + '<MYTREE:TREENODE text="'+@StateName+'">'--+CONVERT(varchar,@@NESTLEVEL)
		INSERT INTO #Nodes
		VALUES(REPLICATE(@Tab,@Tabs) +
			--dbo.sprint6('<MYTREE:TREENODE DEFAULTSTYLE="%s" tabID="%s.%s.%s" id="%s" text="%s">',@Color,@Tabs,@GrandParentID,@KidID,@KidID,@StateName)
			'<MYTREE:TREENODE DEFAULTSTYLE="'+@Color+'" tabID="'+CONVERT(varchar,@Tabs)+'.'+CONVERT(varchar,@GrandParentID)+'.'+CONVERT(varchar,@KidID)+'" id="'+CONVERT(varchar,@KidID)+'" text="'+CONVERT(varchar,@StateName)+'">
'
		)
		--INSERT INTO #ShowSFKids VALUES(@GrandParentID,@StateFromID,@KidID,@Tabs)
		EXEC s_ShowSFKids @TableName,@principal_id,@GrandParentID,@KidID,@Tabs_
		--PRINT REPLICATE(@Tab,@Tabs) + '</MYTREE:TREENODE>'+@CR
		INSERT INTO #Nodes VALUES(REPLICATE(@Tab,@Tabs) + '</MYTREE:TREENODE>'+@CR)
	END
	---SELECT TOP 1 @Pos=Pos,@StateName=KidName,@KidID=KidID,@Color=Color FROM @KidsTable WHERE Pos>@Pos
	FETCH NEXT FROM curKids INTO @StateName,@KidID,@Color
END

CLOSE curKids
DEALLOCATE curKids










